/*
 * PatrolClientView.java
 */

package patrolclient;

import com.surelution.core.drivers.CheckRecord;
import com.surelution.core.drivers.CommunicationException;
import com.surelution.core.drivers.Connection;
import com.surelution.core.drivers.DriverFactory;
import com.surelution.core.drivers.FunctionNotSupportedException;
import com.surelution.core.drivers.Wand;
import com.surelution.core.drivers.kh_v2.KhLibrary;
import java.awt.Color;
import java.awt.Cursor;
import java.awt.Font;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.ParserConfigurationException;
import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.jdesktop.application.Action;
import org.jdesktop.application.ResourceMap;
import org.jdesktop.application.SingleFrameApplication;
import org.jdesktop.application.FrameView;
import org.jdesktop.application.TaskMonitor;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;
import java.util.UUID;
import javax.swing.Timer;
import javax.swing.Icon;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.protocol.HTTP;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

/**
 * The application's main frame.
 */
public class PatrolClientView extends FrameView {

    public PatrolClientView(SingleFrameApplication app) {
        super(app);
        initComponents();
        
    }

    @Action
    public void showAboutBox() {
        if (aboutBox == null) {
            JFrame mainFrame = PatrolClientApp.getApplication().getMainFrame();
            aboutBox = new PatrolClientAboutBox(mainFrame);
            aboutBox.setLocationRelativeTo(mainFrame);
        }
        PatrolClientApp.getApplication().show(aboutBox);
    }
    @Action
    public void showToolBox() {
        JFrame mainFrame1 = PatrolClientApp.getApplication().getMainFrame();
        toolClient = new ClientTool(mainFrame1);
        toolClient.setLocationRelativeTo(mainFrame1);
        toolClient.setResizable(false);
        toolClient.setTitle("管理员工具");
        PatrolClientApp.getApplication().show(toolClient);
    }
    
    @Action
    public void updateTool() {
        JFrame mainFrame1 = PatrolClientApp.getApplication().getMainFrame();
        updateTool = new UpdateTool(mainFrame1);
        updateTool.setLocationRelativeTo(mainFrame1);
        updateTool.setResizable(false);
        PatrolClientApp.getApplication().show(updateTool);
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        mainPanel = new javax.swing.JPanel();
        jButton2 = new javax.swing.JButton();
        lblConnect = new javax.swing.JLabel();
        lblVerify = new javax.swing.JLabel();
        lblDownload = new javax.swing.JLabel();
        lblLocalSave = new javax.swing.JLabel();
        lblUpload = new javax.swing.JLabel();
        lblFinish = new javax.swing.JLabel();
        jLabel1 = new javax.swing.JLabel();
        menuBar = new javax.swing.JMenuBar();
        javax.swing.JMenu fileMenu = new javax.swing.JMenu();
        jMenuItem1 = new javax.swing.JMenuItem();
        javax.swing.JMenuItem exitMenuItem = new javax.swing.JMenuItem();
        javax.swing.JMenu helpMenu = new javax.swing.JMenu();
        updateItem = new javax.swing.JMenuItem();
        javax.swing.JMenuItem aboutMenuItem = new javax.swing.JMenuItem();

        mainPanel.setName("mainPanel"); // NOI18N

        org.jdesktop.application.ResourceMap resourceMap = org.jdesktop.application.Application.getInstance(patrolclient.PatrolClientApp.class).getContext().getResourceMap(PatrolClientView.class);
        jButton2.setText(resourceMap.getString("jButton2.text")); // NOI18N
        jButton2.setName("jButton2"); // NOI18N
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        lblConnect.setForeground(new java.awt.Color(102, 102, 102));
        lblConnect.setText(resourceMap.getString("lblConnect.text")); // NOI18N
        lblConnect.setName("lblConnect"); // NOI18N

        lblVerify.setForeground(new java.awt.Color(102, 102, 102));
        lblVerify.setText(resourceMap.getString("lblVerify.text")); // NOI18N
        lblVerify.setName("lblVerify"); // NOI18N

        lblDownload.setForeground(new java.awt.Color(102, 102, 102));
        lblDownload.setText(resourceMap.getString("lblDownload.text")); // NOI18N
        lblDownload.setName("lblDownload"); // NOI18N

        lblLocalSave.setForeground(new java.awt.Color(102, 102, 102));
        lblLocalSave.setText(resourceMap.getString("lblLocalSave.text")); // NOI18N
        lblLocalSave.setName("lblLocalSave"); // NOI18N

        lblUpload.setForeground(new java.awt.Color(102, 102, 102));
        lblUpload.setText(resourceMap.getString("lblUpload.text")); // NOI18N
        lblUpload.setName("lblUpload"); // NOI18N

        lblFinish.setForeground(new java.awt.Color(102, 102, 102));
        lblFinish.setText(resourceMap.getString("lblFinish.text")); // NOI18N
        lblFinish.setName("lblFinish"); // NOI18N

        jLabel1.setFont(resourceMap.getFont("jLabel1.font")); // NOI18N
        jLabel1.setText(resourceMap.getString("jLabel1.text")); // NOI18N
        jLabel1.setName("jLabel1"); // NOI18N

        org.jdesktop.layout.GroupLayout mainPanelLayout = new org.jdesktop.layout.GroupLayout(mainPanel);
        mainPanel.setLayout(mainPanelLayout);
        mainPanelLayout.setHorizontalGroup(
            mainPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(mainPanelLayout.createSequentialGroup()
                .add(72, 72, 72)
                .add(mainPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                    .add(lblFinish, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 163, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                    .add(jButton2)
                    .add(lblVerify, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 209, Short.MAX_VALUE)
                    .add(lblDownload, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 209, Short.MAX_VALUE)
                    .add(lblLocalSave, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 209, Short.MAX_VALUE)
                    .add(lblUpload, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 209, Short.MAX_VALUE)
                    .add(org.jdesktop.layout.GroupLayout.TRAILING, jLabel1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 209, Short.MAX_VALUE)
                    .add(lblConnect, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 209, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                .add(168, 168, 168))
        );
        mainPanelLayout.setVerticalGroup(
            mainPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(mainPanelLayout.createSequentialGroup()
                .add(41, 41, 41)
                .add(jLabel1, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 26, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                .add(18, 18, 18)
                .add(jButton2)
                .add(27, 27, 27)
                .add(lblConnect)
                .add(16, 16, 16)
                .add(lblVerify)
                .add(18, 18, 18)
                .add(lblDownload)
                .add(18, 18, 18)
                .add(lblLocalSave)
                .add(14, 14, 14)
                .add(lblUpload)
                .add(18, 18, 18)
                .add(lblFinish)
                .addContainerGap(27, Short.MAX_VALUE))
        );

        menuBar.setName("menuBar"); // NOI18N

        fileMenu.setText(resourceMap.getString("fileMenu.text")); // NOI18N
        fileMenu.setName("fileMenu"); // NOI18N

        javax.swing.ActionMap actionMap = org.jdesktop.application.Application.getInstance(patrolclient.PatrolClientApp.class).getContext().getActionMap(PatrolClientView.class, this);
        jMenuItem1.setAction(actionMap.get("showToolBox")); // NOI18N
        jMenuItem1.setIcon(resourceMap.getIcon("jMenuItem1.icon")); // NOI18N
        jMenuItem1.setText(resourceMap.getString("jMenuItem1.text")); // NOI18N
        jMenuItem1.setName("jMenuItem1"); // NOI18N
        fileMenu.add(jMenuItem1);

        exitMenuItem.setAction(actionMap.get("quit")); // NOI18N
        exitMenuItem.setIcon(resourceMap.getIcon("exitMenuItem.icon")); // NOI18N
        exitMenuItem.setText(resourceMap.getString("exitMenuItem.text")); // NOI18N
        exitMenuItem.setName("exitMenuItem"); // NOI18N
        fileMenu.add(exitMenuItem);

        menuBar.add(fileMenu);

        helpMenu.setText(resourceMap.getString("helpMenu.text")); // NOI18N
        helpMenu.setName("helpMenu"); // NOI18N

        updateItem.setAction(actionMap.get("updateTool")); // NOI18N
        updateItem.setIcon(resourceMap.getIcon("updateItem.icon")); // NOI18N
        updateItem.setText(resourceMap.getString("updateItem.text")); // NOI18N
        updateItem.setName("updateItem"); // NOI18N
        helpMenu.add(updateItem);
        updateItem.getAccessibleContext().setAccessibleName(resourceMap.getString("updateItem.AccessibleContext.accessibleName")); // NOI18N

        aboutMenuItem.setAction(actionMap.get("showAboutBox")); // NOI18N
        aboutMenuItem.setIcon(resourceMap.getIcon("aboutMenuItem.icon")); // NOI18N
        aboutMenuItem.setText(resourceMap.getString("aboutMenuItem.text")); // NOI18N
        aboutMenuItem.setToolTipText(resourceMap.getString("aboutMenuItem.toolTipText")); // NOI18N
        aboutMenuItem.setName("aboutMenuItem"); // NOI18N
        helpMenu.add(aboutMenuItem);

        menuBar.add(helpMenu);

        setComponent(mainPanel);
        setMenuBar(menuBar);
    }// </editor-fold>//GEN-END:initComponents

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        // TODO add your handling code here:
            SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            Date d = new java.util.Date();
            String dateTime = ft.format(d);
            System.out.println(dateTime + " Start to run!");
            boolean connectFlag;
            boolean verifyDeviceFlag = false;
            boolean downloadFlag = false;
            boolean uploadFlag = false;
            PatrolClientApp.getApplication().getMainFrame().setCursor(Cursor.WAIT_CURSOR);
            connectFlag = this.connectDevice();
            System.out.println("connectFlag=" + connectFlag);
            //start to verify Device 
            if (connectFlag){
                System.out.println("链接设备成功！");
                this.lblConnect.setForeground(Color.blue);
                this.lblConnect.setText("连接设备成功！");
                verifyDeviceFlag = this.verifyDevice();
            }
            else{
                this.lblConnect.setForeground(Color.red);
                this.lblConnect.setText("连接设备失败，请重试!");
                System.out.println("连接设备失败，请重试！");
            }
            //start to downloading!
            if (verifyDeviceFlag){
                try {
                    downloadFlag = this.download();
                    System.out.println("downloadFlag=" + downloadFlag);
                } catch (CommunicationException ex) {
                    Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
                } catch (ClassNotFoundException ex) {
                    Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
                } catch (SQLException ex) {
                    Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            else{
                System.out.println("设备验证失败，请联系管理员！");
            }
            
            //start to upload information!
            if (verifyDeviceFlag){
            try {
                    try {
                        uploadFlag = this.upload();
                        System.out.println("uploadFlag=" + uploadFlag);
                        System.out.println("Finished!");
                        }catch (SAXException ex) {
                            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
                        }
                } catch (ClassNotFoundException ex) {
                    Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
                } catch (SQLException ex) {
                    Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
                }
//            }
            }
            PatrolClientApp.getApplication().getMainFrame().setCursor(Cursor.DEFAULT_CURSOR);
    }//GEN-LAST:event_jButton2ActionPerformed
//
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton2;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JMenuItem jMenuItem1;
    private javax.swing.JLabel lblConnect;
    private javax.swing.JLabel lblDownload;
    private javax.swing.JLabel lblFinish;
    private javax.swing.JLabel lblLocalSave;
    private javax.swing.JLabel lblUpload;
    private javax.swing.JLabel lblVerify;
    private javax.swing.JPanel mainPanel;
    private javax.swing.JMenuBar menuBar;
    private javax.swing.JMenuItem updateItem;
    // End of variables declaration//GEN-END:variables

    private Wand wand;
    private List<CheckRecord> record;
    private List<Date> record_dropping;
    private InputStream uploadResponse;
    private static Config config = new Config();
    private static final String HOST = config.getFullApplicationUrl();
    
    
    private JDialog aboutBox;
    private JDialog toolClient;
    private JDialog updateTool;
    private String wandSn;
    
    private boolean connectDevice() {
        Properties prop = new Properties();
        prop.put("type", KhLibrary.MT_USB);
//        prop.put("port", " COM3");
        prop.put("baut", 460800);
        Connection conn;
        System.out.println("connectDevice...");
        try {
            conn = DriverFactory.buildConnection("com.surelution.core.drivers.kh_v2.KhConnection", prop );
            this.lblConnect.setForeground(Color.red);
            this.lblConnect.setText("请将设备连接到电脑,然后再次上传！");
            wand = conn.prepareWand();
            wandSn = wand.getId();
            System.out.println(wand.getId());
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
        }
//        this.lblConnect.setForeground(Color.blue);
//        this.lblConnect.setText("连接设备成功！");
        return !"  ".equals(wand.getId());
    }
    
    private boolean verifyDevice() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        System.out.println("VerifyDevice...");
        try {
            
            if ("  ".equals(wandSn)){
                System.out.println("连接设备失败，无法验证，请重试！");
                this.lblConnect.setForeground(Color.red);
                this.lblConnect.setText("连接设备失败，无法验证，请重试！");
                return false;
            }
            StringBuilder query = new StringBuilder(HOST);
            query.append("/clientGate/shakeHands?wandSn=");
            query.append(wandSn);
            query.append("&terminalTime=");
            query.append(sdf.format(wand.getTime()));
            query.append("&stationTime=");
            query.append(sdf.format(new Date()));
            System.out.println(query.toString());
            HttpClient client = new DefaultHttpClient();
            HttpGet get = new HttpGet(query.toString());
            try {
                HttpResponse response = client.execute(get);
                InputStream is = response.getEntity().getContent();
                DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                DocumentBuilder builder;
                builder = factory.newDocumentBuilder();
                Document doc;
                doc = builder.parse(is);
                doc.getDocumentElement().normalize();
                NodeList nodeList = doc.getElementsByTagName("code");
                if(nodeList != null) {
                    Node codeNode = nodeList.item(0);
                    String code = codeNode.getTextContent();
                    if("FOUND-WAND".contains(code)) {
                        System.out.println("设备校验成功！");
                        this.lblVerify.setForeground(Color.blue);
                        this.lblVerify.setText("设备校验成功！");
                        return true;
                    }
                    else {
                        System.out.println("verify is failed!");
                    }
                }
            } catch (SAXException ex) {
                Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ParserConfigurationException ex) {
                Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                System.out.println("服务器连接失败，无法验证设备，请联系管理员！");
                this.lblVerify.setForeground(Color.red);
                this.lblVerify.setText("服务器连接失败，无法验证设备，请联系管理员！");
                Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            }
            
        } catch (CommunicationException ex) {
            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
        }
      this.lblVerify.setForeground(Color.red);
      this.lblVerify.setText("设备校验失败，请联系管理员！");
      return false;
    }
    
    private boolean download() throws CommunicationException, ClassNotFoundException, SQLException {
        record = wand.getNewRecord();
        try {
            record_dropping = wand.getDroppings();
        } catch (FunctionNotSupportedException ex) {
            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
        }
        boolean localSaveFlag= this.localSave();
        if (localSaveFlag){
            this.lblDownload.setForeground(Color.blue);
            this.lblDownload.setText("下载记录成功！");
            System.out.println("本地下载记录成功!");
            this.lblLocalSave.setForeground(Color.blue);
            this.lblLocalSave.setText("本地保存成功！");
            System.out.println("本地保存成功！");
            return true;
        }
        else{
             this.lblDownload.setForeground(Color.blue);
             this.lblDownload.setText("无下载记录！");
             System.out.println("无下载记录！");
             this.lblLocalSave.setForeground(Color.blue);
             this.lblLocalSave.setText("已保存巡检信息！");
             System.out.println("已保存巡检信息！");
             return false;
        }
    }
    
    private boolean localSave() throws ClassNotFoundException, SQLException {
        java.sql.Connection conn = prepareConnection();
        PreparedStatement ps_dropping = conn.prepareStatement("insert into wand_dropping(id, wand_sn,dropping_time, uploaded) values(?,?,?,?)");
        if(!record_dropping.isEmpty()) {
            for(Date d : record_dropping) {
                ps_dropping.setString(1, UUID.randomUUID().toString());
                ps_dropping.setString(2, wandSn);
                ps_dropping.setDate(3, new java.sql.Date(d.getTime()));
                System.out.println("dropping time..." + new java.sql.Date(d.getTime()));
                ps_dropping.setBoolean(4, false);
                ps_dropping.execute();
            }
            System.out.println("有新设备撞击记录，需要上传！");
            System.out.println("dropping sql command:" + ps_dropping.toString());
            ps_dropping.close();
        }
         PreparedStatement ps = conn.prepareStatement("insert into raw_patrol_info(id, wand_sn, poi_sn, patrol_time, download_time, uploaded) values(?,?,?,?,?,?)");
         if(!record.isEmpty()) {
            for(CheckRecord r : record) {
                ps.setString(1, UUID.randomUUID().toString());
                ps.setString(2, wandSn);
                ps.setString(3, r.getCardNo());
                ps.setDate(4, new java.sql.Date(r.getDate().getTime()));
                ps.setDate(5, new java.sql.Date(System.currentTimeMillis()));
                ps.setBoolean(6, false);
                ps.execute();
            }
            System.out.println("有新巡检信息，需要进行上传！");
            System.out.println("patrol sql command:" + ps.toString());
            ps.close();
            conn.close();
            return true;
        }
         else{
             return false;
         }
    }
    //upload to web server
    private boolean upload() throws ClassNotFoundException, SQLException, SAXException {
        StringBuilder info = new StringBuilder();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        java.sql.Connection conn = prepareConnection();
        System.out.println("uploading... ");
        PreparedStatement ps = conn.prepareStatement("select id, wand_sn, poi_sn, patrol_time, download_time from raw_patrol_info where uploaded=false");
        ResultSet result = ps.executeQuery();
        ps.close();
        conn.close();
        System.out.println("Before info:"+info);
        while(result.next()) {
            String id = result.getString(1);
            String poiSn = result.getString(3);
            Date patrolTime = result.getTimestamp(4);
//            Date downloadTime = result.getTimestamp(5);
            info.append(id);
            info.append(",");
            info.append(wandSn);
            info.append(",");
            info.append(poiSn);
            info.append(",");
            info.append("111");
            info.append(",");
            info.append(sdf.format(patrolTime));
//            info.append(",");
//            info.append(downloadTime);
            info.append("\n");
        }
        System.out.println("After info:"+info.toString());

        HttpPost post = new HttpPost(HOST + "/clientGate/patrolItemUpload");
        List<NameValuePair> list = new ArrayList<NameValuePair>();
        //print all records into local server！
//        System.out.println("info:" + info.toString());
        list.add(new BasicNameValuePair("record", info.toString()));
        String uploadDropping = this.getLocalDBDropping();
        list.add(new BasicNameValuePair("dropping", uploadDropping));
        try {
            post.setEntity(new UrlEncodedFormEntity(list,HTTP.UTF_8));
        } catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        HttpClient client = new DefaultHttpClient();
        this.lblUpload.setForeground(Color.red);
        this.lblUpload.setText("因网络问题，导致上传服务器失败，请重试！");
        try {
            HttpResponse response = client.execute(post);
            this.lblUpload.setForeground(Color.blue);
            this.lblUpload.setText("上传服务器成功！");
            uploadResponse = response.getEntity().getContent();
            System.out.println("上传服务器成功！");
//            String uploadDropping = this.getLocalDBDropping();
            boolean uploadCallFlag = this.uploadCallback(info);
            if (uploadCallFlag){
                this.lblFinish.setForeground(Color.blue);
                System.out.println("uploadCallFlag=" + uploadCallFlag);
                return true;
            }
        } catch (IOException ex) {
            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
     private String getLocalDBDropping() throws SQLException, ClassNotFoundException{
        StringBuilder info = new StringBuilder();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        java.sql.Connection conn = prepareConnection();
        System.out.println("uploading dropping... ");
        PreparedStatement ps = conn.prepareStatement("select id, wand_sn, dropping_time from wand_dropping where uploaded=false");
        ResultSet result = ps.executeQuery();
        while(result.next()) {
            System.out.println("uploading new dropping record!");
            String id = result.getString(1);
            Date dropping_time = result.getTimestamp(3);
            info.append(id);
            info.append(",");
            info.append(wandSn);
            info.append(",");
            info.append(sdf.format(dropping_time));
            info.append("\n");
        }
        result.close();
        ps.close();
        conn.close();
        
        return info.toString();
     }

    
    private boolean uploadCallback(StringBuilder info) throws IOException, SAXException, ClassNotFoundException, SQLException {
        try {
                //uploadResponse
                //update local db to updated
                DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                DocumentBuilder builder;
                builder = factory.newDocumentBuilder();
                Document doc;
                doc = builder.parse(uploadResponse);
                doc.getDocumentElement().normalize();
                NodeList nodeList = doc.getElementsByTagName("result");
                System.out.println("nodelength=" +  nodeList.getLength());
                System.out.println( "info=" + info);
                java.sql.Connection conn = prepareConnection();
                PreparedStatement ps = conn.prepareStatement("update raw_patrol_info set uploaded=true where id = ?");
                for(int i = 0; i < nodeList.getLength(); i++) {
                    org.w3c.dom.Node node = nodeList.item(i);
                    String remoteID = node.getAttributes().getNamedItem("remoteId").getTextContent();
                    ps.setString(1, remoteID);
                    ps.execute();
                    System.out.println("patrol sql command:" + ps.toString());
                 }
                ps.close();
                
                ps = conn.prepareStatement("update wand_dropping set uploaded=true");
                System.out.println("dropping sql command:" + ps.toString());
                ps.execute();
                ps.close();
                
                conn.close();
                if ((nodeList.getLength()==0)&(!"".equals(info.toString()))){
                    this.lblUpload.setForeground(Color.red);
                    this.lblUpload.setText("因网络问题，导致最后一步失败，请重新上传！");
                    System.out.println("因网络问题，导致最后一步失败，请重新上传！");                    
                    return false;
                }
                else {
                        return true;
                        }
        } catch (ParserConfigurationException ex) {
            Logger.getLogger(PatrolClientView.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    private java.sql.Connection prepareConnection() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbcDriver");
        java.sql.Connection conn = DriverManager.getConnection("jdbc:hsqldb:file:database/mydb","sa","");
        return conn;
    }
}
